const mysql = require('mysql2/promise')
const dotenv = require('dotenv')

dotenv.config()

// 数据库配置
const dbConfig = {
  host: 'localhost', // 使用 localhost 以启用 socket 连接
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || 'wxy12345',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0,
  port: process.env.DB_PORT || 3306,
  socketPath: '/tmp/mysql.sock', // 添加 socket 路径
  ssl: false,
  enableKeepAlive: true,
  keepAliveInitialDelay: 0
}

console.log('Database configuration:', {
  ...dbConfig,
  password: '****' // 隐藏密码
})

// 创建数据库连接池（不指定数据库）
const initialPool = mysql.createPool(dbConfig)

// 创建数据库
const createDatabase = async () => {
  try {
    console.log('Attempting to create database...')
    const connection = await initialPool.getConnection()
    await connection.query(`
      CREATE DATABASE IF NOT EXISTS ai_chat
      CHARACTER SET utf8mb4
      COLLATE utf8mb4_unicode_ci
    `)
    console.log('Database created or already exists')
    connection.release()
  } catch (err) {
    console.error('Database creation failed:', err.message)
    if (err.code === 'ER_ACCESS_DENIED_ERROR') {
      console.error('Access denied. Please check your MySQL username and password.')
    }
    throw err
  }
}

// 创建实际使用的连接池（指定数据库）
const pool = mysql.createPool({
  ...dbConfig,
  database: process.env.DB_NAME || 'ai_chat'
})

const connectDB = async () => {
  try {
    console.log('Attempting to connect to database...')
    // 先创建数据库
    await createDatabase()

    // 然后连接到数据库
    const connection = await pool.getConnection()
    console.log('MySQL connection successful')

    // 测试查询
    const [result] = await connection.query('SELECT 1')
    console.log('Test query successful:', result)

    connection.release()
  } catch (err) {
    console.error('MySQL connection failed:', err)
    if (err.code === 'ER_ACCESS_DENIED_ERROR') {
      console.error('Access denied. Please check your MySQL username and password.')
    } else if (err.code === 'ECONNREFUSED') {
      console.error('Connection refused. Please check if MySQL server is running.')
    }
    throw err
  }
}

// 创建数据库表
const initDB = async () => {
  try {
    console.log('Initializing database tables...')
    const connection = await pool.getConnection()

    // 创建用户表
    await connection.query(`
      CREATE TABLE IF NOT EXISTS users (
        id INT PRIMARY KEY AUTO_INCREMENT,
        username VARCHAR(50) UNIQUE NOT NULL,
        email VARCHAR(100) UNIQUE NOT NULL,
        password VARCHAR(255) NOT NULL,
        api_key VARCHAR(255) DEFAULT '',
        theme VARCHAR(20) DEFAULT 'dark',
        model VARCHAR(50) DEFAULT 'deepseek-chat',
        temperature FLOAT DEFAULT 0.7,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      )
    `)
    console.log('Users table created or verified')

    // 创建聊天记录表
    await connection.query(`
      CREATE TABLE IF NOT EXISTS chats (
        id INT PRIMARY KEY AUTO_INCREMENT,
        user_id INT NOT NULL,
        title VARCHAR(255) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id)
      )
    `)
    console.log('Chats table created or verified')

    // 创建消息表
    await connection.query(`
      CREATE TABLE IF NOT EXISTS messages (
        id INT PRIMARY KEY AUTO_INCREMENT,
        chat_id INT NOT NULL,
        role ENUM('user', 'assistant') NOT NULL,
        content TEXT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (chat_id) REFERENCES chats(id)
      )
    `)
    console.log('Messages table created or verified')

    connection.release()
    console.log('Database initialization completed successfully')
  } catch (err) {
    console.error('Database initialization failed:', err)
    throw err
  }
}

module.exports = { pool, connectDB, initDB }
